/*****************************************************************************
 * File: DbInterface.cs 
 * Date: 03/15/2012
 * Author: Phil Allison (stoneguard@gmail.com)
 * Purpose: This is the primary class through with all other helper classes interact with the database.
 * 
 * copyright (c) 2001-2012, Phil Allison. Under the MIT License http://www.opensource.org/licenses/mit-license.php  
 * Feel free to reuse and modify this code as long as credit is given to the original author.
 * Modified code must be noted in this section.
 *****************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace sgsBase
{
	[Serializable]public class DbInterface : DbConnections
	{
		private SqlCommand _cmd;
		public bool IgnoreError = false; // sometimes SQL errors can be safely ignored within the code. This allows for such situations

		/// <summary>
		/// places single quotes around strings and allows for containing single quotes within the string
		/// </summary>
		/// <param name="item">The item to be quoted. May be any item.</param>
		/// <returns></returns>
		public static string quote(object item)
		{
			string itm = item.ToString();
			// first, remove any quotes on the ends
			// then double up any quotes inside and add quotes back to the ends
			itm = itm.Trim('\'');
			itm = itm.Replace("'", "''");
			return "'" + itm + "'";
		}
		public static string quote(DateTime item)
		{
			if (item == null)
				return "''";

			string dt = item.ToShortDateString();
			return quote(dt);
		}


		public DbInterface(string Database = "")
			: base(Database)
		{

		}

		/// <summary>
		/// Query - Return  a datatable of values from the database. 
		/// </summary>
		/// <param name="sql">SQL Query to be run against the database.</param>
		/// <returns></returns>
		public DataTable Query(string sql)
		{
			DataTable ret = runSql(sql);
			return ret;

			//DataTable retDT = new DataTable(); // allocate a datatable to hold whatever is returned.
			//Open(); //open the connection to the database.
			//try
			//{
			//    _cmd = CreateCommand(sql, System.Data.CommandType.Text);
			//    retDT = runsql();
			//}
			//catch(Exception er)
			//{
			//    //string addl_stack = Environment.StackTrace;
			//    if (IgnoreError == false)
			//        throw new Exception("Query Error", er);
			//}
			//finally
			//{
			//    _cmd.Dispose(); // need to dispose of the SqlCommand 
			//    Close(); // close the connection if it is open
			//}

			//return retDT;
		}

		/// <summary>
		/// QueryScalar - Return a single requested value in string format
		/// </summary>
		/// <param name="sql">SQL Query to be run against the database.</param>
		/// <returns>object - the item returned may be any value, but is returned in an object.</returns>
		public object QueryScalar(string sql)
		{
			object ret = runScalar(sql);
			return ret;

			//object ret = "";
			//Open();

			//try
			//{
			//    _cmd = CreateCommand(sql, System.Data.CommandType.Text);
			//    ret  = runscalar();
			//}
			//catch (Exception er)
			//{
			//    //string addl_stack = Environment.StackTrace;
			//    if (IgnoreError == false)
			//        throw new Exception("Query Error", er);
			//}
			//finally
			//{
			//    _cmd.Dispose();
			//    Close();
			//}

			//return ret;
		}

		/// <summary>
		/// NonQuery - runs a SQL query against the databse and does not expect any results.
		/// </summary>
		/// <param name="sql">SQL Query to be run against the database.</param>
		/// <returns>bool - indicates whether the query successfully ran.</returns>
		public bool NonQuery(string sql)
		{
			bool ret = runSqlNon(sql);

			return ret;
		}

		/// <summary>
		/// QueryConcat - Run a query and take all returned rows and put them into a comma delimited single row.
		/// </summary>
		/// <param name="sql">SQL Query to be run against the database.</param>
		/// <returns>string - all rows concatenated together into a single row, each value separated by commas.</returns>
		public string QueryConcat(string sql)
		{
			DataTable dt = runSql(sql);

			// if no rows were returned, return an empty string
			if (dt.Rows.Count == 0)
			{
				return "";
			}
			//loop through all rows returned, pull off the first value.
			// if it is a numeric value, no need for quotes, but if it is a string, put quotes around it.
			string ret = "";
			foreach (DataRow dr in dt.Rows)
			{
				if (ret != "")
					ret += ",";

				if (CASTHelper.isNumeric(dr[0]))
					ret += dr[0].ToString();
				else
				{
					ret += quote(dr[0]);
				}
			}
			return ret;
		}


		/// <summary>
		/// SpNew - initialize a new stored procedure call. Parameters for the stored procedure can be added after it is 
		/// initialized and before the stored procedure is invoked.
		/// </summary>
		/// <param name="spname">the name of the stored procedure that will be called.</param>
		/// <returns>bool - indicating the procedure was successfully initialize</returns>
		public bool SpNew(string spname)
		{
			Open();
			_cmd = CreateCommand(spname, CommandType.StoredProcedure);

			return true;
		}

		/// <summary>
		/// SpAddParam - Add a parameter for the stored procedure. 
		/// </summary>
		/// <param name="parmname">Name of parameter, either prefixed with @ or not.</param>
		/// <param name="parmtype">SQL Parameter type.</param>
		/// <param name="parmval">value to be passed in through parameter.</param>
		public void SpAddParam(string parmname, SqlDbType parmtype, object parmval)
		{
			SqlParameter param = _cmd.Parameters.Add(parmname, parmtype);
			param.Value = parmval;
		}

		/// <summary>
		/// SpRun - Runs the stored procedure and returns any results in a DataTable
		/// </summary>
		/// <returns>DataTable - results of the stored procedure</returns>
		public DataTable SpRun()
		{
			DataTable dt = runSql(""); // this makes sure it does not try and re-create the command processor
			return dt;
		}

		/// <summary>
		/// SpRunNon - Runs stored procedure without expecting any returned results
		/// </summary>
		public void SpRunNon()
		{
			runSqlNon("");
		}

		#region private functions

		/// <summary>
		/// runSql - the private function which runs the SQL and then creates a DataTable from the returned information.
		/// </summary>
		/// <param name="sql">SQL Query to be run against the database.</param>
		/// <returns></returns>
		private DataTable runSql(string sql)
		{
			DataTable dt = new DataTable();
			Open();
			if (_cmd == null && sql != "")
				_cmd = CreateCommand(sql, System.Data.CommandType.Text);

			try
			{
				SqlDataReader Reader = _cmd.ExecuteReader();

				if (Reader.IsClosed)
					return dt;

				bool read = Reader.Read();

				// find all the fields in the returned data.
				// Create the columns for the DataTable.
				// add in each row from the returned data into the DataTable.
				for (int i = 0; i < Reader.FieldCount; i++)
					dt.Columns.Add(Reader.GetName(i), Reader.GetFieldType(i));

				object[] returnRow = new object[Reader.FieldCount];
				while (read)
				{
					int count = Reader.GetValues(returnRow);
					dt.Rows.Add(returnRow);
					returnRow = new object[Reader.FieldCount];
					read = Reader.Read();
				}

				Reader.Close();
			}
			catch (Exception er)
			{
				if (IgnoreError == false)
				{
					//put together the error string so the user can see what failed.
					string cmd = _cmd.CommandText;

					foreach (SqlParameter sp in _cmd.Parameters)
					{
						cmd += " " + sp.Value;
					}
					throw new Exception("Query Error: " + cmd, er);
				}
			}
			finally
			{
				_cmd.Dispose();
				_cmd = null;
				Close();
			}

			return dt;
		}

		/// <summary>
		/// runSqlNon - Run a SL statement against the database without expecting any return values. 
		/// </summary>
		/// <param name="sql">SQL Query to be run against the database.</param>
		/// <returns>true on success, an exception otherwise. </returns>
		private bool runSqlNon(string sql)
		{
			DataTable dt = new DataTable();
			Open();
			if (_cmd == null && sql != "")
				_cmd = CreateCommand(sql, System.Data.CommandType.Text);

			try
			{
				int i = _cmd.ExecuteNonQuery();
			}
			catch (Exception er)
			{
				if (IgnoreError == false)
				{
					//put together the error string so the user can see what failed.
					string cmd = _cmd.CommandText;

					foreach (SqlParameter sp in _cmd.Parameters)
					{
						cmd += " " + sp.Value;
					}
					throw new Exception("NonQuery Error: " + cmd, er);
				}
			}
			finally
			{
				_cmd.Dispose();
				_cmd = null;
				Close();
			}

			return true;
		}

		/// <summary>
		/// runScalar - return one single value from thwe query passed in, such as a count() or SUM() statement.
		/// </summary>
		/// <param name="sql">SQL Query to be run against the database.</param>
		/// <returns></returns>
		/// NOTE: If more than 1 value or row is returned, only the very first column of the first row will be returned back
		/// to the calling procedure.
		private object runScalar(string sql)
		{
			object ret = "";

			Open();
			_cmd = CreateCommand(sql, System.Data.CommandType.Text);

			try
			{
				SqlDataReader Reader = _cmd.ExecuteReader();

				bool read = Reader.Read();

				object[] returnRow = new object[Reader.FieldCount];
				if (read)
				{
					ret = Reader.GetValue(0);
				}
				Reader.Close();
			}
			catch (Exception er)
			{
				if (IgnoreError == false)
				{
					string cmd = _cmd.CommandText;

					foreach (SqlParameter sp in _cmd.Parameters)
					{
						cmd += " " + sp.Value;
					}
					//string addl_stack = Environment.StackTrace;
					throw new Exception("Scalar Error: " + cmd, er);
				}
			}
			finally
			{
				_cmd.Dispose();
				_cmd = null;
				Close();
			}

			return ret;
		}

		#endregion
	}
}
